package com.dubbo.mapper;

import com.dubbo.domain.Category;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.util.List;
import java.util.Map;

/**
 * 分类Mapper
 * @author wujunyuan
 * @date 2018年12月14日  上午11:11:07
 */
public interface CategoryMapper extends Mapper<Category>{

	/**
	 * 查询顶级系列列表
	 * @author wujunyuan
	 * @param pageTotal 
	 * @param pageStart 
	 * @date 2018年12月14日  下午2:16:43
	 * @return
	 */
	@Select("SELECT" + 
			"	gc.id AS categoryId," + 
			"   gc.name AS categoryName,"+
			"   gc.parentId AS categoryParentId,"+
			"   gc.depth AS categoryDepth,"+
			"   gc.parse AS categoryParse,"+
			"   gc.sort AS categorySort"+
			" FROM" + 
			"	g_category gc" + 
			" WHERE" + 
			"	gc.parentId = 0" +
			" AND " +
			"   gc.status = 0 " +
			" ORDER BY gc.sort"+
			"  LIMIT #{pageStart},#{pageTotal}")
	List<Map<String, Object>> findCategoryParentList(@Param("pageStart") Long pageStart, @Param("pageTotal") Long pageTotal);

	/**
	 * 查询二级系列列表
	 * @author wujunyuan
	 * @param pageTotal 
	 * @param pageStart 
	 * @date 2018年12月14日  下午2:29:17
	 * @param long1
	 * @return
	 */
	@Select("SELECT" + 
			"	gc.id AS categoryId," +
			"   gc.name AS categoryName,"+
			"   gc.parentId AS categoryParentId,"+
			"   gc.depth AS categoryDepth,"+
			"   gc.parse AS categoryParse,"+
			"   gc.sort AS categorySort,"+
			"	p.id AS picId," + 
			"	p.title AS picTitle," + 
			"	p.url AS picUrl," + 
			"	p.sort AS picSort," + 
			"	CASE p.isMain WHEN 1 THEN '主图' ELSE '非主图' END AS isMain" + 
			" FROM" + 
			"	g_category gc" + 
			" LEFT JOIN g_category_pic_key gcpk ON gc.id = gcpk.categoryId" + 
			" JOIN p_pic p ON gcpk.picId = p.id" + 
			" WHERE" + 
			"	gc.parentId = #{parentId}" + 
			" AND p.isMain = 1"+
			" AND gc.status = 0 " +
			" ORDER BY gc.sort" + 
			" LIMIT #{pageStart},#{pageTotal}")
	List<Map<String, Object>> findCategorySecList(@Param("parentId") Long parentId,
                                                  @Param("pageStart") Long pageStart, @Param("pageTotal") Long pageTotal);

	/**
	 * 获取一级列表总记录数
	 * @author wujunyuan
	 * @date 2018年12月14日  下午4:00:09
	 * @return
	 */
	@Select("SELECT" + 
			"	COUNT(gc.id)" + 
			" FROM" + 
			"	g_category gc"+
			" WHERE gc.depth=1 " +
			" AND gc.status = 0 " )
	Long findCategoryCount();
	
	/**
	 * 获取二级列表总记录数
	 * @author wujunyuan
	 * @date 2018年12月26日  下午5:25:34
	 * @return
	 */
	@Select("SELECT" + 
			"	COUNT(gcy.id)" + 
			" FROM" + 
			"	g_category gcy" + 
			" WHERE" + 
			"	gcy.depth = 2 " +
			" AND gcy.status = 0")
	Long findCategoryParentCount();

	/**
	 * 获取系列详情
	 * @author wujunyuan
	 * @date 2018年12月15日  上午9:38:37
	 * @param categoryId 系列id
	 * @return
	 */
	@Select("SELECT " + 
			"	gcro.content " + 
			"FROM " + 
			"	g_category_ratio gcro " + 
			"WHERE " + 
			"	gcro.categoryId = #{categoryId}")
	Map<String, Object> findCategorydeDails(@Param("categoryId") Long categoryId);

	/**
	 * 查询系列对应的配图
	 * @author wujunyuan
	 * @date 2018年12月15日  上午11:58:54
	 * @param categoryId 系列id
	 * @return
	 */
	@Select("SELECT" + 
			"	p.title," + 
			"	p.url," + 
			"	p.sort," + 
			"	CASE p.isMain WHEN 1 THEN '主图' ELSE '非主图' END AS isMain" + 
			" FROM" + 
			"	g_category_pic_key gcpk" + 
			" LEFT JOIN p_pic p ON gcpk.picId = p.id" + 
			" WHERE" + 
			"	gcpk.categoryId = #{categoryId}"+
			" AND p.isMain = 1 ")
	List<Map<String, Object>> findCategoryPicList(@Param("categoryId") Long picId);
	
	/**
	 * 查询二级系列列表(不分页并根据sort排序)
	 * @author wujunyuan
	 * @date 2018年12月19日  下午4:11:50
	 * @param categoryId
	 * @return
	 */
	@Select("SELECT" + 
			"	gc.id AS categoryId," +
			"   gc.name AS categoryName,"+
			"   gc.parentId AS coryParenategtId,"+
			"   gc.depth AS categoryDepth,"+
			"   gc.parse AS categoryParse,"+
			"   gc.sort AS categorySort,"+
			"	p.id AS picId," + 
			"	p.title AS picTitle," + 
			"	p.url AS picUrl," + 
			"	p.sort AS picSort," + 
			"	CASE p.isMain WHEN 1 THEN '主图' ELSE '非主图' END AS isMain" + 
			" FROM" + 
			"	g_category gc" + 
			" LEFT JOIN g_category_pic_key gcpk ON gc.id = gcpk.categoryId" + 
			" JOIN p_pic p ON gcpk.picId = p.id" + 
			" WHERE" + 
			"	gc.parentId = #{parentId}" + 
			" AND p.isMain = 1 AND gc.status = 0 "+
			" ORDER BY gc.sort")
	List<Map<String, Object>> findCategoryList(@Param("parentId") Long parentId);
	

	/**
	 * 根据categoryId获取次级系列总数
	 * @author wujunyuan
	 * @date 2018年12月26日  下午5:40:25
	 * @param categoryId
	 * @return
	 */
	@Select("SELECT COUNT(gcy.id) FROM g_category gcy WHERE parentId = #{categoryId} AND gcy.status = 0")
	Long findCategorySecCount(@Param("categoryId") Long categoryId);

	/**
	 * 获取一级系列列表(不分页)
	 * @author wujunyuan
	 * @date 2018年12月26日  下午5:56:46
	 * @return
	 */
	@Select("SELECT" + 
			"	gc.id AS categoryId," + 
			"   gc.name AS categoryName"+
			" FROM" + 
			"	g_category gc" + 
			" WHERE" + 
			"	gc.parentId = 0 AND gc.status = 0" +
			" AND " +
			"   gc.status = 0 " +
			"	ORDER BY gc.`sort`"
			)
	List<Map<String, Object>> findCategoryParentListNotLimit();

	/**
	 * 根据parentId获取二级列表总记录数
	 * @author wujunyuan
	 * @date 2018年12月29日  下午5:21:50
	 * @param parentId 父级id
	 * @return
	 */
	@Select("SELECT COUNT(gc.id) FROM g_category gc WHERE gc.parentId =#{parentId} AND gc.status=0")
	Long findSecondListCount(@Param("parentId") Long parentId);

	/**
	 * 获取下单首页二级系列
	 * @author wujunyuan
	 * @date 2019年1月9日  下午4:53:22
	 * @param countPageStart
	 * @param pageTotal
	 * @return
	 */
	/*@Select("SELECT" + 
			"	gc.id AS categoryId," + 
			"   gc.name AS categoryName,"+
			"   gc.parentId AS categoryParentId,"+
			"   gc.depth AS categoryDepth,"+
			"   gc.parse AS categoryParse,"+
			"   gc.sort AS categorySort"+
			" FROM" + 
			"	g_category gc" + 
			" WHERE" + 
			"	gc.depth = 2 " +
			" ORDER BY gc.`name`"+
			"  LIMIT #{pageStart},#{pageTotal}")
	List<Map<String, Object>> findPicCategoryList(@Param("pageStart")long countPageStart,@Param("pageTotal")Long pageTotal);*/
	
	/**
	 * 获取分类信息
	 * @author chenluqiang
	 * @date 2019年1月18日 下午2:40:29 
	 * @param categoryId
	 * @return
	 */
	@Select("SELECT " + 
			"	gcy.id AS 'categoryId', " + 
			"	gcy.`name` AS categoryName, " + 
			/*"	gcy.parentId, " + 
			"	gcy.sort, " + 
			"	gcy.depth, " + 
			"	gcy.parse, " + */
			"	gcy2.`name` AS parentName " + 
			"FROM " + 
			"	g_category gcy " + 
			"LEFT JOIN g_category gcy2 ON gcy2.id = gcy.parentId " + 
			"WHERE " + 
			"	gcy.id = #{categoryId} " +
			" AND " +
			"   gcy.status = 0 ")
	Map<String, Object> getCategoryByCategoryId(@Param("categoryId") Integer categoryId);
	
	/**
	 * 获取分类id
	 * @author chenluqiang
	 * @date 2019年1月18日 下午8:20:51 
	 * @param goodsid
	 * @return
	 */
	@Select("SELECT " + 
			"	ggs.categoryId " + 
			"FROM " + 
			"	g_goods ggs " + 
			"WHERE " + 
			"	ggs.id = #{goodsId}")
	Long getCategoryIdByGoodsId(@Param("goodsId") String goodsid);

}